const express = require('express');
const router = express.Router();
const pool = require('../db');
const authMiddleware = require('../middleware/auth');

// GET /api/logs?page=1&limit=10&sort_by=time_stamp&sort_order=desc&q=...&user_id=...
router.get('/logs', authMiddleware, async (req, res) => {
  try {
    const page = Math.max(1, parseInt(req.query.page, 10) || 1);
    const limit = Math.max(1, parseInt(req.query.limit, 10) || 10);
    const offset = (page - 1) * limit;

    const allowedSorts = {
      id: 'l.id',
      time_stamp: 'l.time_stamp',
      user_name: 'u.user_name',
      ip: 'l.ip'
    };

    const sortBy = typeof req.query.sort_by === 'string' ? req.query.sort_by : null;
    let sortOrder = typeof req.query.sort_order === 'string' ? req.query.sort_order.toLowerCase() : null;
    if (sortOrder !== 'asc' && sortOrder !== 'desc') sortOrder = null;

    let orderClause = 'l.time_stamp DESC';
    if (sortBy && allowedSorts[sortBy]) {
      orderClause = `${allowedSorts[sortBy]} ${sortOrder === 'asc' ? 'ASC' : 'DESC'}`;
    }

    // filters
    const q = typeof req.query.q === 'string' && req.query.q.trim() !== '' ? req.query.q.trim() : null;
    const userId = req.query.user_id ? parseInt(req.query.user_id, 10) : null;

    const whereClauses = [];
    const params = [];

    if (q) {
      // search in message, ip, location, tp and obj_json
      whereClauses.push('(l.message LIKE ? OR l.ip LIKE ? OR l.location LIKE ? OR l.tp LIKE ? OR l.obj_json LIKE ?)');
      const like = `%${q}%`;
      params.push(like, like, like, like, like);
    }

    if (userId) {
      whereClauses.push('l.user_id = ?');
      params.push(userId);
    }

    const whereSql = whereClauses.length ? ('WHERE ' + whereClauses.join(' AND ')) : '';

    const sql = `SELECT l.id, l.time_stamp, l.message, l.user_id, u.user_name AS user_name, l.ip, l.location, l.tp, l.obj_json FROM atb_log l LEFT JOIN atb_user u ON l.user_id = u.id ${whereSql} ORDER BY ${orderClause} LIMIT ? OFFSET ?`;

    const qparams = params.concat([limit, offset]);
    const [rows] = await pool.query(sql, qparams);

    // convert time_stamp to Beijing time (UTC+8) to ensure UI shows correct local time
    const adjusted = rows.map(r => {
      if (!r.time_stamp) return r;
      try {
        const tsMs = new Date(r.time_stamp).getTime();
        const beijingMs = tsMs;
        const d = new Date(beijingMs);
        const YYYY = d.getFullYear();
        const MM = String(d.getMonth() + 1).padStart(2, '0');
        const DD = String(d.getDate()).padStart(2, '0');
        const hh = String(d.getHours()).padStart(2, '0');
        const mm = String(d.getMinutes()).padStart(2, '0');
        const ss = String(d.getSeconds()).padStart(2, '0');
        return Object.assign({}, r, { time_stamp: `${YYYY}-${MM}-${DD} ${hh}:${mm}:${ss}` });
      } catch (e) {
        return r;
      }
    });

    // count
    const countSql = `SELECT COUNT(*) AS total FROM atb_log l ${whereSql}`;
    const [countRows] = await pool.query(countSql, params);
    const total = (countRows && countRows[0] && countRows[0].total) ? countRows[0].total : 0;

    res.json({ logs: adjusted, total });
  } catch (err) {
    console.error('Failed to fetch logs:', err);
    res.status(500).json({ message: 'Server error' });
  }
});

module.exports = router;
